package model;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class WriteInformationInToDB {

	public static String writeToDB(String fileName) throws IOException {
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		ResultSet rs = null;
		StringBuilder sb1 = new StringBuilder();
		StringBuilder sb2 = new StringBuilder();
		StringBuilder sb3 = new StringBuilder();
		sb3.append(") value(");
		File f = new File(fileName);
		String t = f.getName();
		String table = t.substring(0, t.lastIndexOf("."));
		System.out.println(table);
		sb2.append("insert into " + table + "(stuNo,name");
		sb1.append("create table " + table + "(id int not null primary key auto_increment,stuNo varchar(20) not null");
		// 表名固定了
		try {
			conn = JDBCUtil.getConnection();
			File file = new File(fileName);
			FileInputStream is = new FileInputStream(file);
			InputStreamReader isr = new InputStreamReader(is, "UTF8");
			BufferedReader bf = new BufferedReader(isr);
			String str;
			int flag = 0, cou = 0;// cou标记提供列数
			String[] titles = null;
			while ((str = bf.readLine()) != null) {
				// 初次建表
				if (flag == 0) {
					titles = str.split("\\s+");
					for (String string : titles) {
						// System.out.println(string+ " 第"+cou);
						if (cou > 0) {
							sb1.append("," + string + "  varchar(20) not null");
						}
						if (cou == 0) {
							sb3.append("?");
						} else {
							sb3.append(",?");
						}
						if (cou >= 2) {
							// sb2.append(string);
							sb2.append("," + string);

						}

						cou++;
					}
					flag = 1;
					sb1.append(");");
					sb3.append(");");
					String createsql = sb1 + "";
					String sss;
					sss = sb3 + "";
					String insertsql = sb2 + sss;
					ps2 = conn.prepareStatement(createsql);
					ps2.executeUpdate();

				}

				else {
					String sss;
					sss = sb3 + "";
					String insertsql = sb2 + sss;
					ps = conn.prepareStatement(insertsql);
					String[] s = str.split("\\s");
					for (int i = 0; i < s.length; i++) {
						ps.setString(i + 1, s[i]);
						// System.out.println();

					}

					ps.executeUpdate();
				}

			}

			System.out.println("成功向数据库插入文本 ");
			// return table;
		} catch (SQLException e) {
			System.out.println("SQLException!");
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			System.out.println("FileNotFound");
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(rs, ps, conn);
		}
		return table;

	}

	public static void deletetable(String tablename) throws IOException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String deletesql = "drop table " + tablename;
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(deletesql);
			ps.executeUpdate();
		} catch (SQLException e) {
			System.out.println("SQLException!");
			e.printStackTrace();
		} finally {
			JDBCUtil.realeaseAll(rs, ps, conn);
		}

	}

}
